package br.com.divulgaqui.persistence.daoimpl;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.Query;

import br.com.divulgaqui.entity.Categoria;
import br.com.divulgaqui.persistence.dao.CategoriaDAO;
import br.com.divulgaqui.persistence.dao.JPAGenericDAO;

public class CategoriaDAOImpl extends JPAGenericDAO<Categoria> implements CategoriaDAO{

	public CategoriaDAOImpl(EntityManager entityManager) {
		super(entityManager);		
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Categoria> listarSubsParentes(Long idParente) {
		StringBuilder sql = new StringBuilder("select c from Categoria c \n");
		sql.append("where c.catParente.id = "+idParente+" \n");
		sql.append("and c.ativo = true \n");
		sql.append("order by c.nome");
		Query query = this.getEntityManager().createQuery(sql.toString());		
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Categoria> listarCategorias() {
		StringBuilder sql = new StringBuilder("from Categoria c \n");		
		sql.append("where c.catParente.id = null \n");
		sql.append("and c.ativo = true \n");
		sql.append("order by c.nome");
		Query query = this.getEntityManager().createQuery(sql.toString());
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Categoria> listarCategoriasInativas() {
		StringBuilder sql = new StringBuilder("from Categoria c \n");
		sql.append("left outer join fetch c.catParente \n");
		sql.append("where c.ativo = false \n");
		sql.append("order by c.nome");
		Query query = this.getEntityManager().createQuery(sql.toString());
		return query.getResultList();
	}

	@Override
	public Categoria pesquisarCategoriaPorNome(Long id,String nome) {
		StringBuilder sql = new StringBuilder("from Categoria c \n");		
		sql.append("where UPPER(c.nome) = \n");
		sql.append("'"+nome.toUpperCase()+"' \n");
		sql.append("and c.id != "+id+"");
		Query query = this.getEntityManager().createQuery(sql.toString());
		Categoria categoria = null;
		try {
			categoria = (Categoria) query.getSingleResult();
		} catch (NoResultException e) {
			return null;
		}
		return categoria;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Categoria> listarCategoriasNivel2() {
		StringBuilder sql = new StringBuilder("from Categoria c \n");
		sql.append("left outer join fetch c.subsParentes \n");
		sql.append("where c.catParente.id != null \n");		
		sql.append("and c.ativo = true \n");
		sql.append("order by c.nome");
		Query query = this.getEntityManager().createQuery(sql.toString());
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Categoria> qtdProdutosPorCategoriaNivel3(Boolean ativa) {
		StringBuilder sql = new StringBuilder("from Categoria c \n");
		sql.append("left outer join fetch c.modelos \n");
		sql.append("where c.catParente.id != null  and c.catParente.catParente.id != null \n");			
		sql.append("order by c.nome");
		Query query = this.getEntityManager().createQuery(sql.toString());
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Categoria> listarCategoriaPorNivel(Integer nivel) {
		StringBuilder sql = new StringBuilder("from Categoria c \n");
		sql.append("left outer join fetch c.subsParentes sp \n");
		if(nivel.intValue() == 1){			
			sql.append("where c.catParente.id = null \n");	
		}else if(nivel.intValue() == 2){			
			sql.append("where c.catParente.id != null \n");	
			sql.append("and c.catParente.catParente.id != null \n");
		}
		sql.append("order by c.nome");
		Query query = this.getEntityManager().createQuery(sql.toString());
		return query.getResultList();
	}

}
